A Generic Procedure for Integration Testing of ETL Procedures

Testing is one of the key factors to any software products’ success and data warehouse systems are no exception. Data warehouse can be tested in different ways (e.g. front-end testing, database testing) but testing the data warehouse’s ETL procedures (sometimes called back-end testing [1]) is probably the most complex and critical data warehouse testing job, because it directly affects the quality of data. Throughout the ETL process, source data is being put through various transformations, from simple algebraic operations to complex procedural modifications. The question is how accurate and reliable is data in the data warehouse after passing through all these transformations and, in the first place, have the data that should have been extracted actually been extracted and subsequently transformed and loaded? In order to attain a certain degree of confidence in the data quality, series of test should be performed on a daily basis. Like any software system, data warehouse’s ETL system can be tested in various fashions, for instance, one can test small isolated ETL components using unit tests or one can test the overall process using integration tests (not to mention other kinds of testing like regression tests, system tests etc. [2]). In this paper we propose a generic procedure for (backend) integration testing of certain aspects of ETL procedures. More precisely, we’re interested in asserting whether all data is accurately transferred throughout the ETL process, that is whether “all counts match”. ETL In order to attain a certain degree of confidence in the quality of the data in the data warehouse it is necessary to perform a series of tests. There are many components (and aspects) of the data warehouse that can be tested, and in this paper we focus on the ETL procedures. Due to the complexity of ETL process, ETL procedure tests are usually custom written, having a very low level of reusability. In this paper we address this issue and work towards establishing a generic procedure for integration testing of certain aspects of ETL procedures. In this approach, ETL procedures are treated as a black box and are tested by comparing their inputs and outputs – datasets. Datasets from three locations are compared: datasets from the relational source(s), datasets from the staging area and datasets from the data warehouse. Proposed procedure is generic and can be implemented on any data warehouse employing dimensional model and having relational database(s) as a source. Our work pertains only to certain aspects of data quality problems that can be found in DW systems. It provides a basic testing foundation or augments existing data warehouse system’s testing capabilities. We comment on proposed mechanisms both in terms of full reload and incremental loading.